package com.wn.dao.impl;

import com.wn.bean.Owner;
import com.wn.dao.OwnerDao;
import com.wn.utils.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author yellow Docter
 * @date 2024 -06-19
 * @desc
 */
public class OwnerDaoImp implements OwnerDao {


    @Override
    public int insertOwner(Owner owner) throws SQLException {

        //开始jdbc操作步骤

        //01 获取conn
        Connection conn = JdbcUtil.getConnection();
        //02  准备sql
        String sql = "insert into owner(user_name,tel,sex,identity,remarks) values (?,?,?,?,?)";
        System.out.println("sql = " + sql);

        //03 拿到ps
        PreparedStatement ps = conn.prepareStatement(sql);
        //04  设置参数
        ps.setObject(1,owner.getUsername());
        ps.setObject(2,owner.getTel());
        ps.setObject(3,owner.getSex());
        ps.setObject(4,owner.getIdentity());
        ps.setObject(5,owner.getRemarks());
        //05 执行
        int i = ps.executeUpdate();
        //06  拿到结果释放资源
        JdbcUtil.close(ps,conn);

        return i;
    }

    @Override
    public List<Owner> list() {

        Connection conn = JdbcUtil.getConnection();
        //02  准备sql
        String sql = "select * from owner";
        System.out.println("sql = " + sql);
        //03 拿到ps
        PreparedStatement ps = null;

        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            List<Owner> list = new ArrayList<Owner>();
            while (rs.next()) {
                Owner owner = new Owner();
                owner.setId(rs.getInt("id"));
                owner.setUsername(rs.getString("user_name"));
                owner.setTel(rs.getString("tel"));
                owner.setSex(rs.getInt("sex"));
                owner.setIdentity(rs.getString("identity"));
                owner.setRemarks(rs.getString("remarks"));

                list.add(owner);
            }
            return list;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtil.close(rs,ps,conn);
        }

    }

    @Override
    public int deleteOwnerById(int id) {

        Connection conn = JdbcUtil.getConnection();
        //02  准备sql
        String sql = "delete from owner where id = ?";
        System.out.println("sql = " + sql);
        PreparedStatement ps = null;
        try {
             ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            int row = ps.executeUpdate();
            return row;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtil.close(ps,conn);
        }
    }

    @Override
    public int updateOwner(Owner owner) {
        //开始jdbc操作步骤

        //01 获取conn
        Connection conn = JdbcUtil.getConnection();
        //02  准备sql
        String sql = "update owner set user_name = ?,tel=?,sex=?,identity=?,remarks=? where id = ?";
        System.out.println("sql = " + sql);

        //03 拿到ps
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);

            //04  设置参数
            ps.setObject(1,owner.getUsername());
            ps.setObject(2,owner.getTel());
            ps.setObject(3,owner.getSex());
            ps.setObject(4,owner.getIdentity());
            ps.setObject(5,owner.getRemarks());
            ps.setObject(6,owner.getId());
            //05 执行
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //06  拿到结果释放资源
            JdbcUtil.close(ps,conn);
        }

    }

    @Override
    public int deleteOwnerByIds(String[] ids) {

        Connection conn = JdbcUtil.getConnection();
        //02  准备sql

        //ids=[1,2,3]
        //delete from owner where id in (1,2,3)
        StringBuilder sb = new StringBuilder();
        sb.append("delete from owner where id in (");
        for (String id : ids) {
            sb.append("?").append(",");
        }
        //把最后一个逗号切掉
        sb.deleteCharAt(sb.length()-1);
        sb.append(")");

        System.out.println("sql = " + sb.toString());
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sb.toString());
            for (int i = 0; i < ids.length; i++) {
                ps.setInt(i + 1,Integer.parseInt(ids[i]));
            }
            int row = ps.executeUpdate();
            return row;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtil.close(ps,conn);
        }
    }

    @Override
    public int count() {

        Connection conn = JdbcUtil.getConnection();
        //02  准备sql
        String sql = "select count(*) from owner";
        System.out.println("sql = " + sql);
        //03 拿到ps
        PreparedStatement ps = null;

        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                int total = (int) rs.getInt(1);
                return total;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            JdbcUtil.close(rs,ps,conn);
        }
        return 0;
    }

    @Override
    public List<Owner> page(int start, int pageSize) {

        Connection conn = JdbcUtil.getConnection();
        //02  准备sql
        String sql = "select * from owner limit ?,?";
        System.out.println("sql = " + sql);
        //03 拿到ps
        PreparedStatement ps = null;

        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,start);
            ps.setInt(2,pageSize);

            rs = ps.executeQuery();

            List<Owner> list = new ArrayList<Owner>();
            while (rs.next()) {
                Owner owner = new Owner();
                owner.setId(rs.getInt("id"));
                owner.setUsername(rs.getString("user_name"));
                owner.setTel(rs.getString("tel"));
                owner.setSex(rs.getInt("sex"));
                owner.setIdentity(rs.getString("identity"));
                owner.setRemarks(rs.getString("remarks"));

                list.add(owner);
            }

            return list;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtil.close(rs,ps,conn);
        }

    }
}
